#!/usr/bin/php

<?php

/**
Version: 1b

photofind - iphoto.duplicate.search.php

More Info:	http://code.google.com/p/photofind

USAGE:

Simple usage; if your iPhoto library resides in the pictures folder in your home directory,
simply set the username and iphoto_lib_name variables below under SETTINGS.

NOTE: It is recommended you use EITHER the MD5 OR Title search.
		Use one at a time, since this allows you to sort photos in an iPhoto smart album more easily.


Running this on a DB of 60,000 photos and 130GB
	-	24 hours to complete MD5 sums
	-	2-3 hours to search for duplicate titles in iPhoto DB
	-	1 hour to search for duplicate MD5 sums in local cache


TODO: Cache titles from iPhoto DB for faster searching from local cache instead of large iPhoto DB.
*/

//////////////////
// SETTINGS - only thing you need to change
//////////////////
$username = 'username';					// your system username (used for path detection)
$iphoto_lib_name = 'iPhoto Library';	// name of the iPhoto library in your Pictures folder
$check_filename = false;				// finds duplicate photos based on photo title
$check_md5 = true;						// finds duplicate photos based on MD5 sum
$refresh = false;						// LEGACY: this deletes the cache DB before beginning. Not recommended or needed.


//////////////////
// IPHOTO SETTINGS
//////////////////
$iphoto_lib_path = '/Users/'.$username.'/Pictures/'.$iphoto_lib_name.'/';
$iphoto_db_path = $iphoto_lib_path.'/iPhotoMain.db';

if (!file_exists($iphoto_db_path)) {
	print "\niPhoto DB could not be found\n";
	exit();
}

print "\nOpening: ".$iphoto_db_path."\n";

if(!$iphoto_db = sqlt_connect($iphoto_db_path))
	exit();

//////////////////
// CACHE SETTINGS
//////////////////
$cache_lib_path = '/Users/'.$username.'/Library/Application Support/iphoto.duplicate.search/';
$cache_db_path = $cache_lib_path.'cache.db';

if($refresh && file_exists($cache_db_path)) {
	unlink($cache_db_path);
}
if(!file_exists($cache_lib_path)) {
	print "\nCreating directory: ".$cache_lib_path;
	if(!mkdir($cache_lib_path)) {
		print "... FAIL!\n";
		exit();
	}
	print "... OK\n";
}

// open local cache sqlite db
print "\nOpening: ".$cache_db_path."\n";

if(!$cache_db = sqlt_connect($cache_db_path))
	exit();

//////////////////
// PREPARE CACHE
//////////////////
$sql = 'CREATE TABLE IF NOT EXISTS iphoto (
		fileimage_pk INTEGER PRIMARY KEY,
		photoinfo_pk INTEGER,
		fileInfo_pk INTEGER,
		flag INTEGER,
		md5 VARCHAR
		)';
$cache_db->exec($sql);

$sql_reset = 'UPDATE iphoto SET flag=0';
$cache_db->exec($sql_reset);

//////////////////
// LOOP THROUGH PHOTOS
//////////////////
$sql_search = 'SELECT
				SqFileImage.primaryKey AS fileimage_pk,
				SqFileImage.photoKey AS photoinfo_pk,
				SqFileImage.imageWidth AS fileimage_width,
				SqFileImage.imageHeight AS fileimage_height,
				SqFileImage.sqFileInfo AS fileInfo_pk,
				SqFileInfo.relativePath AS fileInfo_relativePath,
				SqPhotoInfo.archiveFilename AS photoinfo_archiveFilename
				FROM SqFileImage 
				LEFT JOIN SqPhotoInfo ON SqFileImage.photoKey=SqPhotoInfo.primaryKey
				LEFT JOIN SqFileInfo ON SqFileImage.sqFileInfo=SqFileInfo.primaryKey
				WHERE SqFileImage.imageType=6';

foreach($iphoto_db->query($sql_search) as $row) {
	print "\nScanning Photo PK: ".$row[photoinfo_pk];
	if($check_md5) {
		// search local cache to avoid re-calculating md5 if it already exists
		$sql_cache_search = "SELECT * FROM iphoto WHERE fileimage_pk='".$row[fileimage_pk]."'";
		$temp = $cache_db->query($sql_cache_search);
		if(!$temp->fetch()) {
			// insert into cache with md5
			$sql_insert = 'INSERT INTO iphoto(fileimage_pk,photoinfo_pk,fileInfo_pk,md5)'
							.' VALUES('
							."'".$row[fileimage_pk]."',"
							."'".$row[photoinfo_pk]."',"
							."'".$row[fileInfo_pk]."',"
							."'".md5_file($iphoto_lib_path.$row[fileInfo_relativePath])."')";
			if($cache_db->exec($sql_insert)) {
				print "\nRow ".$cache_db->lastInsertId()." inserted…";
			}
		} else { // re-enable row
			$sql_update = "UPDATE iphoto SET flag=1 WHERE fileimage_pk='".$row[fileimage_pk]."'";
			if(!$cache_db->exec($sql_update)) {
				print "\nERROR! Unable to update flag on row where fileimage_pk=".$row[fileimage_pk]."\n";
				exit();
			}
		}
	}
	
	if($check_filename) {
		// search iphoto DB
		print "\t".$row[photoinfo_archiveFilename];
		$sql_iphoto_search = $iphoto_db->prepare("SELECT * FROM SqPhotoInfo WHERE archiveFilename LIKE :archiveFilename AND primaryKey!=:primaryKey");
		$sql_iphoto_search->execute(array('archiveFilename' => $row[photoinfo_archiveFilename], 'primaryKey' => $row[photoinfo_pk]));
		if($duplicates = $sql_iphoto_search->fetchall()) {
			// update comments in iphoto_db for current working row
			$sql_update = "UPDATE SqPhotoInfo SET comments='duplicate".$row[fileimage_pk]."' WHERE primaryKey='".$row[photoinfo_pk]."'";
			if($iphoto_db->exec($sql_update)) {
				print "\nfile: Row ".$row[photoinfo_pk]." updated…";
			}
		}
		foreach($duplicates as $duplicate_row) {
			// update comments in iphoto_db for all associated rows
			$sql_update = "UPDATE SqPhotoInfo SET comments='duplicate".$row[fileimage_pk]."' WHERE primaryKey='".$duplicate_row[primaryKey]."'";
			if($iphoto_db->exec($sql_update)) {
				print "\nfile: Row ".$duplicate_row[primaryKey]." updated…";
			}
		}
	}
}

//////////////////
// CLEANUP CACHE
//////////////////
$sql = 'DELETE FROM iphoto WHERE flag=0';
if(!$cache_db->exec($sql)) {
	print "\nERROR! Unable to delete untagged photos from cache.\n";
	exit();
}


//////////////////
// COMPARE MD5s
//////////////////
if($check_md5) {
	$count = 0;
	$sql_search = 'SELECT fileimage_pk,photoinfo_pk,fileInfo_pk,md5 FROM iphoto WHERE flag=1';
	foreach($cache_db->query($sql_search) as $row) {
		$cache_check = $cache_db->prepare("SELECT * FROM iphoto WHERE md5 LIKE :md5 AND fileimage_pk!=:fileimage_pk");
		$cache_check->execute(array('md5' => $row[md5], 'fileimage_pk' => $row[fileimage_pk]));
		if($duplicates = $cache_check->fetchall()) {
			$sql_update = "UPDATE SqPhotoInfo SET comments='duplicate".$row[fileimage_pk]."' WHERE primaryKey='".$row[photoinfo_pk]."'";
			if($iphoto_db->exec($sql_update)) {
				print "\nLoop ".$count;
				print "\nmd5: Row ".$row[photoinfo_pk]." updated…";
			}
		}
		foreach($duplicates as $duplicate_row) {
			$sql_update = "UPDATE SqPhotoInfo SET comments='duplicate".$row[fileimage_pk]."' WHERE primaryKey='".$duplicate_row[photoinfo_pk]."'";
			if($iphoto_db->exec($sql_update)) {
				print "\nmd5: Row ".$duplicate_row[photoinfo_pk]." updated…";
			}
		}
		$count++;
		print "\nLoop: ".$count;
	}
}

print "\n";
exit();

function sqlt_connect($path) {
	$dsn = 'sqlite:'.$path;
	try {
		$db = new PDO($dsn);
	} catch (PDOException $e) {
		echo "\nFailed to open DB: " . $e->getMessage();
		return -1;
	}
	return $db;
}

?>